<?php

namespace lico\Tool;

use \PHPExcel;
use \PHPExcel_IOFactory;

//静态类

class Excel
{

    protected $PHPExcel;
    protected $cellKey = array(
        'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
        'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
        'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM',
        'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ',
    );

    public function __construct()
    {
        $this->getHandler();
    }

    /**
     * phpExcel对象
     */
    public function getHandler()
    {
        if (!$this->PHPExcel) {
            $this->PHPExcel = new PHPExcel();
        }
        return $this->PHPExcel;
    }

    /**
     * 表格导出的数据
     */
    protected $exportData = [];
    /**
     * 导出表格数据
     * 注意：分页名如果含有中文会报错！！！
     * startRow 开始行:可选择每页从第几行开始录入
     * merge    是否合并：默认二维数组
     */
    public function export($filename, $startRow = 1, $merge = false)
    {

        $filename = strtr($filename, '/', DS);
        $filename = iconv('UTF-8', 'GBK', $filename);

        if (!file_exists($filename)) {
            return [];
        }

        $PHPExcel = PHPExcel_IOFactory::load($filename);

        //获取表的页数
        $sheetCount = $PHPExcel->getSheetCount();

        for ($page = 0; $page < $sheetCount; $page++) {
            $data = [];
            //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
            $currentSheet = $PHPExcel->getSheet($page);
            //获取总列数
            $allColumn = $currentSheet->getHighestColumn();
            //获取总行数
            $allRow = $currentSheet->getHighestRow();
            //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
            for ($currentRow = $startRow; $currentRow <= $allRow; $currentRow++) {
                //从哪列开始，A表示第一列
                for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
                    //数据坐标
                    $address = $currentColumn . $currentRow;
                    //读取到的数据，保存到数组$data中
                    $cell = $currentSheet->getCell($address)->getValue();

                    if ($cell instanceof \PHPExcel_RichText) {
                        $cell = $cell->__toString();
                    }
                    $data[$currentRow - 1][$currentColumn] = trim($cell);
                }
            }

            if ($merge) {
                $this->exportData = array_merge($this->exportData, $data);
            } else {
                $this->exportData[] = $data;
            }
        }

        return $this->exportData;
    }

    /**
     * 导入相关配置
     */
    protected $import_config = [
        'filename' => 'test.xls', //文件名
        'size' => 2, //单页最大行数
        'headIgnore' => false, //是否头部忽略不录入,第一页一定会录入
        'currentSheet' => 0, //当前页码
        'headStartRow' => 1, //表头开始的行
        'SheetEnd' => false, //活动页是否已写入完毕
        'SheetEndRow' => 0, //活动页结束的行数
    ];
    /**
     * 导入
     * @param array $headMap 中文与字段的字典
     * @param array $data 需要导入的数据
     * @param string $filename 文件名（带后缀）
     */
    public function import($headMap, $data, $filename = '')
    {

        $filename && $this->import_config['filename'] = $filename;

        $head = array_keys($headMap);
        $fieldsArr = array_flip(array_values($headMap));

        $this->PHPExcel->getProperties()
            ->setCreator("lico")
            ->setLastModifiedBy("lico")
            ->setTitle("数据EXCEL导出")
            ->setSubject("数据EXCEL导出")
            ->setDescription("数据EXCEL导出")
            ->setKeywords("excel")
            ->setCategory("result");

        $this->importPage($head, $fieldsArr, $data); //exit;
        ob_end_clean(); //防止乱码

        header('Content-Type: application/vnd.ms-excel;charset=utf-8;');
        header('Content-Disposition: attachment;filename="' . $this->import_config['filename'] . '"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($this->PHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    /**
     * 导出分页
     */
    public function importPage($head, $fieldsArr, $data)
    {
        // pe($head,$fieldsArr,$data);
        $this->import_config['SheetEnd'] = false;
        $headStartRow = $this->import_config['headStartRow'];
        $this->PHPExcel->setActiveSheetIndex($this->import_config['currentSheet']);

        //所有单元格（列）默认宽度
        $this->PHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(25);
        //垂直居中
        $this->PHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $this->PHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        //处理表头
        if (!$this->import_config['headIgnore'] || $this->import_config['currentSheet'] == 0) {
            foreach ($head as $k => $v) {
                $this->PHPExcel->getActiveSheet()->getStyle($this->cellKey[$k] . $headStartRow)->getFont()->setBold(true); //设置是否加粗
                $this->PHPExcel->getActiveSheet()->getStyle($this->cellKey[$k] . $headStartRow)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); //垂直居中
                $this->PHPExcel->getActiveSheet()->setCellValue($this->cellKey[$k] . $headStartRow, $v); //设置表头数据
            }
        } else {
            $headStartRow = 0;
        }

        //处理数据
        $count = count($data);
        if ($count < $this->import_config['size']) {
            $this->import_config['SheetEnd'] = true;
        }
        foreach ($data as $key => $value) {
            if ($key < $this->import_config['size']) {
                $num = $key + $headStartRow + 1;
                foreach ($value as $k => $v) {
                    if (isset($fieldsArr[$k])) {
                        $row = $this->cellKey[$fieldsArr[$k]];
                        $realVal = $v; //可视情况传入字典进行文字格式化
                        $this->PHPExcel->getActiveSheet()->setCellValue($row . $num, $realVal . ' ', \PHPExcel_Cell_DataType::TYPE_STRING);
                    }
                }
                unset($data[$key]);
            } else {
                $this->import_config['SheetEndRow'] = $key;
                $this->PHPExcel->createSheet();
                break; //跳除循环
            }
        }

        $this->PHPExcel->getActiveSheet()->setTitle('page' . ($this->import_config['currentSheet'] + 1));

        if (!$this->import_config['SheetEnd']) {
            $data = array_values($data);
            $this->import_config['currentSheet']++;
            return $this->importPage($head, $fieldsArr, $data);
        } else {
            return true;
        }
    }
}
